use master
go
USE Bank
GO
/********CREATE User PROCEDURE*****/

/******** INSERT User *****/
/*drop proc spInsertUser*/
CREATE PROC spInsertUser
@userID AS nVARCHAR(5),
@password AS NVARCHAR(30),
@fullName AS NVARCHAR(30),
@address AS NVARCHAR(30),
@email AS VARCHAR(30),
@telephone AS NVARCHAR(30),
@gender AS NVARCHAR(30)
AS
	INSERT INTO [User]([userID],[password],[fullName],[address],[email],[telephone],[gender]) 
	VALUES(@userID,@password,@fullName,@address,@email,@telephone,@gender)

GO

/******** UPDATE User *****/
/*drop proc spUpdateUser*/
CREATE PROC spUpdateUser
@userID AS nVARCHAR(5),
@password AS NVARCHAR(30),
@fullName AS NVARCHAR(30),
@address AS NVARCHAR(30),
@email AS VARCHAR(30),
@telephone AS NVARCHAR(30),
@gender AS NVARCHAR(30)
AS
	UPDATE [User] SET [password] = @password,[fullName]=@fullName,[address]=@address,[email]=@email,[telephone]=@telephone,[gender]=@gender
	WHERE [userID] = @userID

GO

/******** GET ALL User *****/
/*drop proc spGetAllUser*/
CREATE PROC spGetAllUser
AS
	SELECT [userID],[password],[fullName],[address],[email],[telephone],[gender] FROM [User]

GO

/******** GET User BY ID *****/
/*drop proc spGetUserByID*/
CREATE PROC spGetUserByID
@UserID AS nVARCHAR(5)
AS
	SELECT [userID],[password],[fullName],[address],[email],[telephone],[gender] FROM [User]
	WHERE [userID] = @UserID
GO

/*drop proc spGetUserByIDandPass*/
CREATE PROC spGetUserByIDandPass
@userID AS nVARCHAR(5),
@password as nVarchar(30)
AS
	SELECT [userID],[password],[fullName],[address],[email],[telephone],[gender] FROM [User]
	WHERE [userID] = @UserID and [password] = @password
GO

CREATE PROC spGetUserByIDandName
@UserID AS nVARCHAR(5),
@fullName as nVarchar(30)
AS
	SELECT [userID],[password],[fullName],[address],[email],[telephone],[gender] FROM [User]
	WHERE [userID] = @UserID and [fullName] = @fullName
GO

/******** SEARCH User  *****/
/*drop proc spSearchUser*/
CREATE PROC spSearchUser
@txtSearch AS nVARCHAR(50)
AS
	SELECT [userID],[password],[fullName],[address],[email],[telephone],[gender] FROM [User]
	WHERE [userID] = @txtSearch OR [fullName] = @txtSearch
	
GO


/******** DELETE User *****/ 
/*drop proc spDeleteUser*/
CREATE PROC spDeletUser
@UserID AS NVARCHAR(5)
AS
	DELETE FROM [User] 
	WHERE [userID] = @UserID

GO


/********CREATE [Transaction] PROCEDURE*****/

/******** INSERT [Transaction] *****/
/*drop proc spInsertTransaction*/
CREATE PROC spInsertTransaction
@accountID AS NVARCHAR(5),
@date AS DATETIME,
@action NVARCHAR(200)

AS
	INSERT INTO [Transaction]([accountID],[date],[action]) 
	VALUES(@accountID,@date,@action)

GO

/******** UPDATE Transaction *****/
/*drop proc spUpdateTransaction*/
CREATE PROC spUpdateTransaction
@transactionID AS int,
@date AS DATETIME,
@action NVARCHAR(200)

AS
	UPDATE [Transaction] SET [date] = @date,[action]=@action
	WHERE [transactionID] = @transactionID

GO

/******** GET ALL Transaction *****/
/*drop proc spGetAllTransaction*/
CREATE PROC spGetAllTransaction
AS
	SELECT [transactionID],[accountID],[date],[action] FROM [Transaction]

GO

/******** GET Transaction BY ID *****/
/*drop proc spGetTransactionByID*/
CREATE PROC spGetTransactionByID
@transactionID AS nVARCHAR(5)
AS
	SELECT [transactionID],[accountID],[date],[action] FROM [Transaction]
	WHERE [transactionID] = @transactionID
GO

/******** SEARCH Transaction by Account *****/
/*drop proc spSearchTransactionByAccount*/
CREATE PROC spSearchTransactionByAccount
@accountID AS nVARCHAR(5)
AS
	SELECT [transactionID],[accountID],[date],[action] FROM [Transaction]
	WHERE [accountID] = @accountID 
GO


/******** DELETE Transaction *****/ 
/*drop proc spDeleteTransaction*/
CREATE PROC spDeleteTransaction
@transactionID AS int
AS
	DELETE FROM [Transaction] 
	WHERE [transactionID] = @transactionID

GO


/********CREATE Account PROCEDURE*****/

/******** INSERT Account *****/
/*drop proc spInsertAccount*/
CREATE PROC spInsertAccount
@accountID AS NVARCHAR(5),
@userID AS NVARCHAR(5),
@balance AS int,
@currencyCode AS NVARCHAR(5),
@withdrawable AS bit,
@depositable AS bit

AS
	INSERT INTO [Account]([accountID],[userID],[balance],[currencyCode],[withdrawable],[depositable]) 
	VALUES(@accountID,@userID,@balance,@currencyCode,@withdrawable,@depositable)

GO

/******** UPDATE Account *****/
/*drop proc spUpdateAccount*/
CREATE PROC spUpdateAccount
@accountID AS NVARCHAR(5),
@balance AS int,
@currencyCode AS NVARCHAR(5),
@withdrawable AS bit,
@depositable AS bit

AS
	UPDATE [Account] SET [balance] = @balance,[currencyCode]=@currencyCode,[withdrawable] = @withdrawable,[depositable]=@depositable
	WHERE [accountID] = @accountID

GO

/******** UPDATE Account *****/
/*drop proc spUpdateBalance*/
CREATE PROC spUpdateBalance
@accountID AS NVARCHAR(5),
@balance AS int

AS
	UPDATE [Account] SET [balance] = @balance
	WHERE [accountID] = @accountID

GO
/******** UPDATE Account *****/
/*drop proc spAddBalance*/
CREATE PROC spAddBalance
@accountID AS NVARCHAR(5),
@balance AS int

AS
	UPDATE [Account] SET [balance] = @balance + [balance]
	WHERE [accountID] = @accountID

GO
/******** GET ALL Account *****/
/*drop proc spGetAllAccount*/
CREATE PROC spGetAllAccount
AS
	SELECT [accountID],[userID],[balance],[currencyCode],[withdrawable],[depositable] FROM [Account]

GO

/******** GET Account BY ID *****/
/*drop proc spGetAccountByID*/
CREATE PROC spGetAccountByID
@accountID AS nVARCHAR(5)
AS
	SELECT [accountID],[userID],[balance],[currencyCode],[withdrawable],[depositable] FROM [Account]
	WHERE [accountID] = @accountID
GO

/*drop proc spGetAccountByTypeIDandUserID*/
CREATE PROC spGetAccountByTypeIDandUserID
@typeID AS nVARCHAR(5),
@userID as nvarchar(5)
AS
	SELECT [accountID],[userID],[typeID],[balance],[currencyCode],[withdrawable],[depositable] FROM [Account]
	WHERE [typeID] = @typeID and [userID] = @userID
GO
/******** DELETE Account *****/ 
/*drop proc spDeleteAccount*/
CREATE PROC spDeleteAccount
@accountID AS NVARCHAR(5)
AS
	DELETE FROM [Account] 
	WHERE [accountID] = @accountID

GO


